package com.datasecops.scannerservices.watermark;

import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.ArrayList;

/**
 * @author: GeXiaoLong
 * @time: 2023/1/12 10:38
 */
@Service
@Slf4j
public class DB2DB {


    @Resource
    private  ImportAndExport importAndExport;

    public Integer dB2ToCsv(String name) throws ClassNotFoundException, SQLException, IOException {
        //（1）使用Java JDBC连接数据库：

        Class.forName("com.ibm.db2.jcc.DB2Driver");

        String url = "jdbc:db2://10.26.18.66:50000/SAMPLE";

        String user = "db2admin";

        String password = "sc.9102";

        String DB = "SAMPLE";

        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }

//（2）构建SQL查询语句，查询出所需要的数据：

        String model = "SQLJ";
        String tableName = name ;
        String models = "NULLID";
        String sql = "SELECT * FROM " + model + "." + tableName;
        //这个是表数据
        Statement stmt = conn.createStatement();

//        ResultSet rs = stmt.executeQuery(sql);

        //这个是表结构
/*        PreparedStatement stmts = conn.prepareStatement("SELECT 'CREATE TABLE ' || TABSCHEMA || '.' || TABNAME || ' (' || LISTAGG ( COLNAME || ' ' || TYPENAME || '(' || LENGTH || ')', ',' ) WITHIN GROUP ( ORDER BY COLNO ) || ')' AS CREATE_TABLE_SQL FROM SYSCAT.COLUMNS WHERE TABSCHEMA = ? AND TABNAME = ? GROUP BY TABSCHEMA,TABNAME");
        stmts.setString(1, "SQLJ");
        stmts.setString(2, "SOULWJ");
        ResultSet rss = stmts.executeQuery();
//        System.out.println(sqls);
        String tableSql = null;
        while (rss.next()) {
            tableSql = rss.getString("CREATE_TABLE_SQL");
            System.out.println(tableSql);
            System.out.println("---------------------------------------------------------------------------------------");
        }*/


        DatabaseMetaData metaData1 = conn.getMetaData();
        ResultSet pkInfo  = metaData1.getPrimaryKeys(null, model, tableName);
        System.out.println(pkInfo  == null);
        StringBuilder surface = new StringBuilder("");
        String primary = null;
        //System.out.println(pkInfo.next());
        while (pkInfo.next()){
            System.out.print("数据库名称:"+pkInfo .getString("TABLE_CAT")+"                  ");
            System.out.print("表名称:"+pkInfo .getString("TABLE_NAME")+"                  ");


            surface.append("CREATE TABLE");
            surface.append(" ");
            surface.append(pkInfo.getString("TABLE_NAME"));
            surface.append(" ");
            surface.append("(");
            surface.append("\n");


            System.out.print("主键列的名称:"+pkInfo .getString("COLUMN_NAME")+"                  ");
            primary = pkInfo .getString("COLUMN_NAME");
            System.out.print("类型:"+pkInfo .getString("PK_NAME")+"                  ");
            System.out.println("");
        }

        ResultSet tableInfo = metaData1.getColumns(null,model,tableName, null);
        while (tableInfo.next()){
            // 表的名字
            System.out.print("表名:"+tableInfo.getString("TABLE_NAME")+"    ");
            // 列的名称
            System.out.print("列名:"+tableInfo.getString("COLUMN_NAME")+"    ");

            // 默认值
            System.out.print("默认值 :"+tableInfo.getString("COLUMN_DEF")+"  ");
            // 字段的类型
            System.out.print("字段的类型:"+tableInfo.getString("TYPE_NAME")+"   ");

            // 是否可以为空
            System.out.print("是否可以为空:"+tableInfo.getString("IS_NULLABLE")+"  ");

            // 是否为自增
            System.out.print("是否为自增:"+tableInfo.getString("IS_AUTOINCREMENT")+" ");
            // 字段说明
            System.out.print("字段说明:"+tableInfo.getString("REMARKS")+"  ");
            // 长度(有时候是错的)
            System.out.print("长度:"+tableInfo.getString("COLUMN_SIZE")+" ");
            surface.append(tableInfo.getString("COLUMN_NAME"));
            surface.append(" ");
            if ("timestamp".equalsIgnoreCase(tableInfo.getString("TYPE_NAME"))){//这里是因为pg和mysql的时间戳类型不一样,暂时这样处理
                surface.append("datetime");
            }else{
                surface.append(tableInfo.getString("TYPE_NAME"));
            }
            //surface.append(tableInfo.getString("TYPE_NAME"));
            surface.append(" ");
            surface.append("(");
            if ("timestamp".equalsIgnoreCase(tableInfo.getString("TYPE_NAME"))){
                surface.append("6");
            }else {
                surface.append(tableInfo.getString("COLUMN_SIZE"));
            }
            //surface.append(tableInfo.getString("COLUMN_SIZE"));
            surface.append(")");
            surface.append(" ");
            surface.append("COLLATE utf8mb4_unicode_ci");
            surface.append(" ");
            if ("NO".equalsIgnoreCase(tableInfo.getString("IS_NULLABLE"))){
                surface.append("NOT NULL");
            }else{
                surface.append("DEFAULT NULL");
            }
            surface.append(" ");
            surface.append("COMMENT");
            surface.append(" ");
            surface.append("'");
            surface.append(tableInfo.getString("REMARKS"));
            surface.append("'");
            surface.append(",");
            surface.append("\n");
            System.out.println();
        }
        surface.append("PRIMARY KEY");
        surface.append(" ");
        surface.append("(");
        surface.append(primary);
        surface.append(")");
        surface.append(" ");
        surface.append("USING BTREE");
        surface.append("\n");
        surface.append(")");
        surface.append(" ");
        surface.append("ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        String st = surface.toString();
        String replace1;
         replace1 = st.replace("TIMESTAMP", "datetime");
        replace1 = replace1.replace("CHARACTER", "char");
        replace1 = replace1.replace("CLOB", "text");
        System.out.println(replace1);
        String ss = "CREATE TABLE userss (\n" +
                "id varchar (32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '主键',\n" +
                "name varchar (100) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '姓名',\n" +
                "gender varchar (50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '性别',\n" +
                "age varchar (10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '年龄',\n" +
                "id_no varchar (50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '身份证号',\n" +
                "created_date datetime (6) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建时间',\n" +
                "created_by varchar (100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建人',\n" +
                "updated_date datetime (6) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '更新时间',\n" +
                "update_by varchar (100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '更新人',\n" +
                "PRIMARY KEY (id) USING BTREE\n" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
        //importData(ss);

        //第二种写法
//        int columeCounts = metaDatas.getColumnCount();//所有字段的数目
//        while (rs.next()) {
//            for (int i = 1; i <= columeCounts; i++){
//                String columnName = metaDatas.getColumnName(i);//获取该列的原始名字
//                String columnLabel = metaDatas.getColumnLabel(i);//获取该列的别名
//                String columnClassName = metaDatas.getColumnClassName(i);//获取该列的(在java中的)数据类型
//                int columnType = metaDatas.getColumnType(i);//获取该列的(在数据库中的)数据类型对应的序号
//                String columnTypeName = metaDatas.getColumnTypeName(i);//获取该列的(在数据库中的)数据类型
//                int scale = metaDatas.getScale(i);//获取该列中小数点右边的位数
//                int columnDisplaySize = metaDatas.getColumnDisplaySize(i);//获取该列的长度
//                boolean autoIncrement = metaDatas.isAutoIncrement(i);//判断该列的值是否自动递增
//                int nullable = metaDatas.isNullable(i);//判断该列的值是否为null
//                String tableName1 = metaDatas.getTableName(i);//获取表名
//                boolean definitelyWritable = metaDatas.isDefinitelyWritable(i);
//                System.out.println();
//            }
//        }
//（3）将查询结果写入CSV文件：
        ResultSet rs = stmt.executeQuery(sql);
        OutputStreamWriter fw = new OutputStreamWriter(new FileOutputStream("/root/your_file.csv"),
                StandardCharsets.UTF_8);

//        OutputStreamWriter fw = new OutputStreamWriter(new FileOutputStream("P:\\text\\your_file.csv"),
//                StandardCharsets.UTF_8);
//        FileWriter fw = new FileWriter("P:\\text\\your_file.csv");
        ResultSetMetaData metaData = rs.getMetaData();
        int columeCount = metaData.getColumnCount();//
        String columnName = metaData.getColumnName(1);
        //String columnName = metaData.getColumnName(1);
        //System.out.println(columeCount);
        String String = "";

        for (int i = 1; i <= columeCount; i++) {
            if (!(i == columeCount)) {
                String = String + metaData.getColumnName(i) + ",";
            } else {
                String = String + metaData.getColumnName(i) + "\n";
            }
        }
        fw.append(String);
        //String outputString = "";
        while (rs.next()) {
            String outputString = "";
            for (int i = 1; i <= columeCount; i++) {
                if (!(i == columeCount)) {
                    if("002ba075-7321-48cb-bbde-154d6d200096".equalsIgnoreCase(rs.getString(i))){
                        System.out.println("11111111111");
                    }
                    String str = rs.getString(i);
                    boolean b = rs.wasNull();
                    //System.out.println(b);
                    int i1 = -1;
                    if (str != null) {
                        i1 = str.indexOf("\n");//如果获取到的字符串中含有换位符\n ,那么i1的值就不是-1,只有不包含的时候才是-1;
                    }
                    if ( i1 != -1){
                        String replace = null;
                        if (str != null){
                           replace = str.replace("\n", "");//把自动生成的换位符去掉
                        }
                        outputString = outputString + replace + ",";
                    }else {
                        if (b){
                            outputString = outputString  + "NULL"+",";
                        }else {
                            outputString = outputString + rs.getString(i) + ",";
                        }
                        //outputString = outputString + rs.getString(i) + ",";
                    }
                } else {
                    outputString = outputString + rs.getString(i) + "\n";
                }
            }
            fw.append(outputString);

        }

        fw.flush();

        fw.close();

        //（4）关闭数据库连接：

        rs.close();

        stmt.close();

        conn.close();

        Integer integer = importData(replace1, tableName, models);
        return integer;
    }
     /**
      *创建新表和导入csv
      * @Description
      * @author GeXiaoLong
      * @time 2023/1/15 11:27
      * @param
      * @param tableName
      * @param models
      * @return
      */
    public Integer importData(String surface, String tableName, String models) throws SQLException, IOException {
//        1. 使用JDBC驱动程序：
//        首先，我们需要准备一个JDBC驱动程序，例如MySQL Connector / J。该程序可以在MySQL的官方网站上下载。下载完成后，我们需要将其添加到我们的classpath中，以便可以在程序中使用它。
//        2. 建立与MySQL数据库的连接：
//        现在，我们可以编写代码来与MySQL数据库建立连接，使用JDBC驱动程序。以下是一个示例：
//        Statement st = null;
//        String url = "jdbc:mysql://10.26.18.58:3306/WatermarkingDatabase";
//        String user = "root";
//        String password = "Wzx.1104";
//
//        Connection con = DriverManager.getConnection(url, user, password);
//       st =  con.createStatement();
//
////        3. 使用LOAD DATA语句导入CSV文件：
////        现在，我们可以使用Java中的PreparedStatement对象来执行LOAD DATA语句，以便将CSV文件导入MySQL数据库。
//
        String loadDataQuery = " LOAD DATA LOCAL INFILE '/root/your_file.csv' INTO TABLE " +
                tableName + " FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\\n' IGNORE 1 LINES";

//
//        st.executeUpdate(loadDataQuery);
//        System.out.println(loadDataQuery);
//
//        PreparedStatement preparedStatement = con.prepareStatement(loadDataQuery);
//
//        preparedStatement.execute();

        log.error("数据库安装程序进来了吗?");
        Statement st = null;
        try {
            log.error("数据库安装程序运行进来了吗?");
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://10.26.18.58:3306/mysql?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=Asia/Shanghai";
            String username = "root";
            String password = "Wzx.1104";
            Connection c = DriverManager.getConnection(url, username, password);
            st = c.createStatement();
            st.executeUpdate("CREATE DATABASE IF NOT EXISTS WatermarkingDatabase");//创建数据库
            st.executeUpdate("USE WatermarkingDatabase");
            ResultSet resultSet = st.executeQuery("SHOW TABLES LIKE 'SOULWJ'");
            while (resultSet.next()) {
                String string = resultSet.getString(1);
                System.out.println(string);
            }


            int i = st.executeUpdate(surface);//创建一张新表
            System.out.println(i);
            //rs = st.executeQuery("select * from student");
            System.out.println(loadDataQuery);
            boolean execute = st.execute(loadDataQuery);
            System.out.println(execute);//导入csv文件
//System.out.println(loadDataQuery);
            //st.executeUpdate( loadDataQuery);
            //st.execute(loadDataQuery);
        } catch (Exception e) {
            log.error("数据库添加程序运行失败,原因是: ", e);
        } finally {
            st.close();
        }
        Integer watermarking = null;
        try {
            watermarking = watermarking(tableName, models);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        return watermarking;
    }
     /**
      *创建加注水印后的新库
      * @Description
      * @author GeXiaoLong
      * @time 2023/2/6 19:41
      * @param
      * @param tableName
      * @param models
      * @return
      */
    public Integer watermarking(String tableName, String models) throws SQLException, IOException, ClassNotFoundException {
        Integer text = importAndExport.text(tableName);
        //对比行数
        Integer integer = ReadCSV();//csv文件的行数
        Integer integer1 = DBRows(tableName);//数据库导入的行数
        ArrayList<String> list = new ArrayList<>();
        log.error("对比行数出来了吗?");
        while ((integer-1) == integer1){
            log.error("对比行数前进来了吗?");
            list = watermarkExport(tableName,models);
            break;
        }
        //创建新库新表
        //把加完水印的表导入到新库新表中
        watermarkingImport(list);

        return text;
    }


    /**
     *水印加注完后的导出(用sql语句方法)
     * @Description
     * @author GeXiaoLong
     * @time 2023/2/6 15:34
     * @param
     * @param tableName
     * @param modelS
     * @return
     */
    public ArrayList<String> watermarkExport(String tableName, String modelS) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://10.26.18.58:3306/mysql?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=Asia/Shanghai";
        String username = "root";
        String password = "Wzx.1104";



        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        //（3）将查询结果写入sql语句中：
        Statement stmt = conn.createStatement();
        String mode = "watermarkingdatabase";
        //String tableName = "dlp_datarisksit_assessment_question_bank";
        String sql = "SELECT * FROM " + mode +"."+tableName;
        ResultSet rs = stmt.executeQuery(sql);
        StringBuilder stringBuilder = new StringBuilder("");
        ArrayList list = new ArrayList();
        ResultSetMetaData metaData = rs.getMetaData();
        int columeCount = metaData.getColumnCount();//
        String string = "";

        for (int i = 1; i <= columeCount; i++) {
            if (!(i == columeCount)) {
                string = string + "\""+ metaData.getColumnName(i) + "\"" + ",";
            } else {
                string = string + "\"" + metaData.getColumnName(i)+ "\"" ;
            }
        }
        //String outputString = "";

        while (rs.next()) {
            String data = "";
            String outputString = "";
            for (int i = 1; i <= columeCount; i++) {
                if (!(i == columeCount)) {
                    String str = rs.getString(i);
                    boolean b = rs.wasNull();
                    //System.out.println(b);
                    int i1 = -1;
                    if (str != null) {
                        i1 = str.indexOf("\n");//如果获取到的字符串中含有换位符\n ,那么i1的值就不是-1,只有不包含的时候才是-1;
                    }
                    if ( i1 != -1){
                        String replaces = null;
                        if (str != null){
                            replaces = str.replace("\n", "");//把自动生成的换位符去掉
                        }
                        outputString = outputString + "\'" + replaces + "\'" + ",";
                    }else {
                        if (b){
                            outputString = outputString  + "NULL" +",";
                        }else {
                            outputString = outputString + "\'" + rs.getString(i) + "\'" + ",";
                        }
                        //outputString = outputString + rs.getString(i) + ",";
                    }
                } else {
                    outputString = outputString + "\'" + rs.getString(i) +"\'";
                    stringBuilder.append("INSERT INTO ");
                    stringBuilder.append(modelS);
                    stringBuilder.append(".");
                    stringBuilder.append("\"");
                    stringBuilder.append(tableName);
                    stringBuilder.append("\"");
                    stringBuilder.append("(");
                    stringBuilder.append(string);
                    stringBuilder.append(")");
                    stringBuilder.append(" VALUES ");
                    stringBuilder.append("(");
                    stringBuilder.append(outputString);
                    stringBuilder.append(")");
                    //stringBuilder.append(";");
                    stringBuilder.append("\n");
                    data = stringBuilder.toString();
                    System.out.println(data);
                    list.add(data);
                    stringBuilder.setLength(0);//这是把stringBuilder的内容清空



                }
            }
            //           String outputString = rs.getString(1) + "," + rs.getString(2) + "," + rs.getString(3) + "\n";

            //stringBuilder.append(outputString);

        }

        //（4）关闭数据库连接：
        rs.close();
        conn.close();

        return list;
    }



    /**
     *获取csv文件的行数
     * @Description
     * @author GeXiaoLong
     * @time 2023/2/6 15:45
     * @param
     * @return
     */
    public Integer ReadCSV() {
        int count = 0;
        try {
            FileReader fr = new FileReader("/root/your_file.csv");
            BufferedReader br = new BufferedReader(fr);
            //count = 0;
            while (br.readLine() != null) {
                count++;
            }
            System.out.println("行数：" + count);
            br.close();
            fr.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return count;
    }

    /**
     *数据库导入的行数
     * @Description
     * @author GeXiaoLong
     * @time 2023/2/6 16:34
     * @param
     * @param tableName
     * @return
     */
    public Integer DBRows(String tableName) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://10.26.18.58:3306/mysql?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=Asia/Shanghai";
        String username = "root";
        String password = "Wzx.1104";
        String mode = "watermarkingdatabase";

        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Statement stmt = conn.createStatement();
        //String sql = "SELECT COUNT(*) FROM ROOT.dlp_datarisksit_assessment_question_bank";

        String sql = "SELECT COUNT(*) FROM " + mode + "." +tableName;

        ResultSet rs = stmt.executeQuery(sql);
        int count = 0;
        if (rs.next()) {
            count = rs.getInt(1);
        }
        return count;
    }


    /**
     *加注完水印后导回源库
     * @Description
     * @author GeXiaoLong
     * @time 2023/2/6 16:38
     * @param
     * @param sql
     * @return
     */
    public void watermarkingImport(ArrayList<String> sql) throws SQLException {

        log.error("数据库安装程序进来了吗?");
        Statement stmt = null;
        try {
            log.error("数据库安装程序运行进来了吗?");
            Class.forName("com.ibm.db2.jcc.DB2Driver");

            String url = "jdbc:db2://10.26.18.66:50000/SAMPLE";

            String user = "db2admin";

            String password = "sc.9102";

            Connection conn = DriverManager.getConnection(url, user, password);
            //System.out.println(sql);
            stmt = conn.createStatement();
            for (String o : sql) {
                //System.out.println(o);
                int i = stmt.executeUpdate(o);
                //System.out.println(i);
            }
            // int i = stmt.executeUpdate(sql);
            //System.out.println(i);
        } catch (Exception e) {
            log.error("数据库添加程序运行失败,原因是: ",e);
        } finally {
            stmt.close();
        }

        log.error("加注水印的程序进来了吗3");
//        OracleDB oracleDB = new OracleDB();
//        oracleDB.watermarking();
    }




    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException{

//        String strs = "I love china , \n my moyherland";
//        int china = strs.indexOf("moyherland");
//        int chinas = strs.indexOf("chinas");
//        int i = strs.indexOf("\n");
//
//        strs = strs.replace("\n", "");
//        System.out.println(strs);
//
//
//        String loadDataQuery = "LOAD DATA LOCAL INFILE 'E:/DB/your_file.csv' INTO TABLE " +
//                "userss FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\\n' IGNORE 1 LINES\n";
//
//        System.out.println(loadDataQuery);
        DB2DB db2DB = new DB2DB();
        String tableName = "performer".toUpperCase();
        db2DB.dB2ToCsv(tableName);
        //importData(surface);
    }

}
